package Server;

import java.sql.*;
import java.util.*;

public class DB {

    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/airconditionsystem" +
            "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    public static final String USER = "root";
    public static final String PASSWORD = "admin";
    public Connection connection = null;
    private PreparedStatement pStmt = null;
    private ResultSet rs = null;

    DB() {
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String getToken(int length) {
        String str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        Random random = new Random();
        StringBuilder sBuffer = new StringBuilder();
        for (int i = 0; i < length; i++) {
            int number = random.nextInt(62);
            sBuffer.append(str.charAt(number));
        }
        return sBuffer.toString();
    }

    //TODO
    public boolean roomInit(String roomId, String roomIp, double nowTmp) {//房间初始化
        boolean state = false;
        try {
            String sql = "INSERT INTO room VALUES (?, ?, null,false,null,?,null,0,0,null,null,0,0.0)";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            pStmt.setString(2, roomIp);
            pStmt.setDouble(3, nowTmp);
            pStmt.executeUpdate();
            state = true;
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return state;
    }

    // 用户登录时鉴别角色 返回 role + token 字符串
    public String retRole(String adminId, String password) {
        String role = "";
        String token = "";
        String valueString = "";
        try {
            String sql = "SELECT user_auth, user_token FROM user WHERE user_id=? and user_password=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, adminId);
            pStmt.setString(2, password);
            rs = pStmt.executeQuery();
            if (rs.next()) {
                role = rs.getString("user_auth");
                token = rs.getString("user_token");
                valueString = role + "," + token;
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return valueString;
    }

    // TODO : 管理员登出时销毁token ***************************
//    public boolean logout(String adminId) {
//        boolean state = false;
//        try {
//            String sql = "SELECT user_token FROM user WHERE user_id=?";
//            pStmt = connection.prepareStatement(sql);
//            pStmt.setString(1, adminId);
//            String token = null;
//            rs = pStmt.executeQuery();
//            if (rs.next()) {
//                token = rs.getString("user_token");
//            }
//            if (null != token) {    // adminID对应token存在,证明已登录,则需要销毁token
//                String sql2 = "UPDATE user SET user_token=null where user_id=?";
//                pStmt = connection.prepareStatement(sql2);
//                pStmt.setString(1, adminId);
//                pStmt.executeUpdate();
//                state = true;
//            }
//        }
//        catch (SQLException e) {
//            e.printStackTrace();
//        }
//        finally {
//            try {
//                if (pStmt != null) {
//                    pStmt.close();
//                }
//            }
//            catch (SQLException e) {
//                e.printStackTrace();
//            }
//        }
//        return state;
//    }

    public List<Map<String, Object>> getRoomList() {//获取所有房间列表
        List<Map<String, Object>> roomList = new ArrayList<>();
        try {
            String sql = "SELECT room_id,room_occupy FROM room ";
            pStmt = connection.prepareStatement(sql);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> roomInfo = new HashMap<>();
                roomInfo.put("roomId", rs.getString(1));
                roomInfo.put("idle", !rs.getBoolean(2));
                roomList.add(roomInfo);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        System.out.println(roomList);
        return roomList;
    }

    public int verifyUser(String token) {
        int role = User.NOT_EXIST;
        try {
            String sql = "SELECT user_auth FROM user WHERE user_token=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, token);
            rs = pStmt.executeQuery();
            if (rs.next()) {
                role = rs.getInt("user_auth");
            }
            else {
                String sql2 = "SELECT room_id FROM room WHERE room_token=?";
                pStmt = connection.prepareStatement(sql2);
                pStmt.setString(1, token);
                rs = pStmt.executeQuery();
                if (rs.next()) {
                    role = User.GUEST;
                }
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return role;
    }

    // 管理员开房 返回token token长度为0，开房失败
    public String openRoom(String roomId, double defaultTmp) {
        String token = "";
        try {
            String sql = "SELECT room_occupy,room_now_temp FROM room WHERE room_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            rs = pStmt.executeQuery();
            if (rs.next() && !rs.getBoolean("room_occupy")) { //房间存在且没被占用
                double nowTmp = rs.getDouble("room_now_temp");
                String sql2 = "UPDATE room SET room_occupy = true,room_default_temp=?,room_set_temp=?," +
                        "room_token=?,room_checking_time=LOCALTIME() WHERE room_id=?";
                token += getToken(16);//随机数生成
                pStmt = connection.prepareStatement(sql2);
                pStmt.setDouble(1, defaultTmp);
                pStmt.setDouble(2, defaultTmp);
                pStmt.setString(3, token);
                pStmt.setString(4, roomId);
                pStmt.executeUpdate();

                String sql3 = "INSERT INTO bill VALUES (LOCALTIME(), ?, true, ?, ?, 0, 0)";
                pStmt = connection.prepareStatement(sql3);
                pStmt.setString(1, roomId);
                pStmt.setDouble(2, nowTmp);
                pStmt.setDouble(3, defaultTmp);
                pStmt.executeUpdate();
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return token;
    }

    // 房客调目标温度、调风速
    public boolean set(String token, int tmp, int wind) {
        boolean state = true;
        try {
            String sql = "SELECT room_id ,room_power FROM room WHERE room_token=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, token);
            rs = pStmt.executeQuery();
            String roomId = rs.getString(1);
            if (rs.next()) {
                int power = rs.getInt(2);
                if (power == 0) {
                    String sql2 = "UPDATE room SET room_power=1 WHERE room_token=?";
                    pStmt = connection.prepareStatement(sql2);
                    pStmt.setString(1, token);
                    pStmt.executeUpdate();
                }
                if (tmp != -1) {
                    String sql3 = "UPDATE room SET room_set_temp=? WHERE room_token=?";
                    pStmt = connection.prepareStatement(sql3);
                    pStmt.setInt(1, tmp);
                    pStmt.setString(2, token);
                    pStmt.executeUpdate();
                    sql3 = "UPDATE bill SET room_set_temp=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql3);
                    pStmt.setInt(1, tmp);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                }
                if (wind != -1) {
                    String sql4 = "UPDATE room SET room_set_wind=? WHERE room_token=?";
                    pStmt = connection.prepareStatement(sql4);
                    pStmt.setString(2, token);
                    pStmt.setInt(1, wind);
                    pStmt.executeUpdate();
                    sql4 = "UPDATE bill SET room_set_wind=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql4);
                    pStmt.setInt(1, wind);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                }
            }
        }
        catch (SQLException e) {
            state = false;
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                state = false;
                e.printStackTrace();
            }
        }
        return state;
    }

    public List<Map<String, Object>> seeRoomInfo(String roomId) {//roomIdList:(201,202,203)
        List<Map<String, Object>> roomInfoList = new ArrayList<>();
        try {
            String sql = "SELECT room_id ,room_power,room_set_temp,room_now_temp,room_set_wind FROM room WHERE room_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> roomInfo = new HashMap<>();
                roomInfo.put("roomId", rs.getString("room_id"));
                roomInfo.put("power", rs.getInt("room_power"));
                roomInfo.put("setTmp", rs.getDouble("room_set_temp"));
                roomInfo.put("nowTmp", rs.getDouble("room_now_temp"));
                roomInfo.put("wind", rs.getInt("room_set_wind"));
                roomInfoList.add(roomInfo);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return roomInfoList;
    }

    public List<Map<String, Object>> seeAllRoomInfo() {//查看所有被占用房间信息（room_occupy=1） 返回字典列表
        List<Map<String, Object>> roomInfoList = new ArrayList<>();
        try {
            String sql = "SELECT room_id ,room_power,room_set_temp,room_now_temp,room_set_wind FROM room WHERE room_occupy=true";
            pStmt = connection.prepareStatement(sql);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> roomInfo = new HashMap<>();
                roomInfo.put("roomId", rs.getString("room_id"));
                roomInfo.put("power", rs.getInt("room_power"));
                roomInfo.put("setTmp", rs.getDouble("room_set_temp"));
                roomInfo.put("nowTmp", rs.getDouble("room_now_temp"));
                roomInfo.put("wind", rs.getInt("room_set_wind"));
                roomInfoList.add(roomInfo);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return roomInfoList;
    }

    // 控制房间空调
    public boolean controlRoom(String roomId, int power, double tmp, int wind) {
        boolean state = true;
        String sql, sql2;
        try {
            if (roomId.equals("-1")) {
                if (power != -1) {
                    sql = "UPDATE room SET room_power=? ";
                    pStmt = connection.prepareStatement(sql);
                    pStmt.setInt(1, power);
                    pStmt.executeUpdate();
                    if (power == 0) {
                        sql2 = "UPDATE bill SET room_set_wind=0 ";
                        pStmt = connection.prepareStatement(sql2);
                        pStmt.executeUpdate();
                    }
                }
                if (tmp != -1) {
                    sql = "UPDATE room SET room_set_temp=? ";
                    pStmt = connection.prepareStatement(sql);
                    pStmt.setDouble(1, tmp);
                    pStmt.executeUpdate();
                    sql2 = "UPDATE bill SET room_set_temp=? ";
                    pStmt = connection.prepareStatement(sql2);
                    pStmt.setDouble(1, tmp);
                    pStmt.executeUpdate();
                }
                if (wind != -1) {
                    sql = "UPDATE room SET room_set_wind=? ";
                    pStmt = connection.prepareStatement(sql);
                    pStmt.setInt(1, wind);
                    pStmt.executeUpdate();
                    sql2 = "UPDATE bill SET room_set_wind=? ";
                    pStmt = connection.prepareStatement(sql2);
                    pStmt.setInt(1, wind);
                    pStmt.executeUpdate();

                }
            }
            else {
                if (power != -1) {
                    sql = "UPDATE room SET room_power=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql);
                    pStmt.setInt(1, power);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                    if (power == 0) {
                        sql2 = "UPDATE bill SET room_set_wind=0 WHERE room_id=?";
                        pStmt = connection.prepareStatement(sql2);
                        pStmt.setString(1, roomId);
                        pStmt.executeUpdate();
                    }
                }
                if (tmp != -1) {
                    sql = "UPDATE room SET room_set_temp=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql);
                    pStmt.setDouble(1, tmp);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                    sql2 = "UPDATE bill SET room_set_temp=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql2);
                    pStmt.setDouble(1, tmp);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                }
                if (wind != -1) {
                    sql = "UPDATE room SET room_set_wind=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql);
                    pStmt.setInt(1, wind);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                    sql2 = "UPDATE bill SET room_set_wind=? WHERE room_id=?";
                    pStmt = connection.prepareStatement(sql2);
                    pStmt.setInt(1, wind);
                    pStmt.setString(2, roomId);
                    pStmt.executeUpdate();
                }
            }
        }
        catch (SQLException e) {
            state = false;
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                state = false;
                e.printStackTrace();
            }
        }
        return state;
    }

    // 开关空调 对bill表中只通过room_set_wind判断是否开机
    public boolean open(String token, int power) {
        boolean state = true;
        try {
            String sql = "UPDATE room SET room_power=? WHERE room_token=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setInt(1, power);
            pStmt.setString(2, token);
            pStmt.executeUpdate();
            if (power == 0) {
                String sql2 = "SELECT room_id FROM room WHERE room_token=?";
                pStmt = connection.prepareStatement(sql2);
                pStmt.setString(1, token);
                rs = pStmt.executeQuery();
                String roomId = rs.getString(1);
                String sql3 = "UPDATE room SET room_set_wind=0 WHERE room_id=?";
                pStmt = connection.prepareStatement(sql3);
                pStmt.setString(1, roomId);
                pStmt.executeUpdate();
            }
        }
        catch (SQLException e) {
            state = false;
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                state = false;
                e.printStackTrace();
            }
        }
        return state;
    }

    //退房
    public boolean checkOut(String roomId) {
        boolean state = true;
        try {
            String sql = "UPDATE room SET room_leaving_time=LOCALTIME(),room_token=null WHERE room_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            pStmt.executeUpdate();
        }
        catch (SQLException e) {
            state = false;
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                state = false;
                e.printStackTrace();
            }
        }
        return state;
    }

    // 删除房间
    public boolean deleteRoom(String roomId) {
        boolean state = true;
        try {
            String sql = "DELETE FROM room WHERE room_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            pStmt.executeUpdate();
        }
        catch (SQLException e) {
            state = false;
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                state = false;
                e.printStackTrace();
            }
        }
        return state;
    }

    // 删除房客信息
    public boolean deleteGuestInfo(String roomId) {
        boolean state = true;
        try {
            String sql = "DELETE FROM bill WHERE room_id=? ";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            pStmt.executeUpdate();
            sql = "UPDATE room SET room_occupy=false,room_fee=0.0 WHERE room_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            pStmt.executeUpdate();
        }
        catch (SQLException e) {
            state = false;
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                state = false;
                e.printStackTrace();
            }
        }
        return state;
    }

    public String simpleCost(String roomId) {
        String simpleCost = "";
        try {
            String sql = "SELECT room_checking_time,room_leaving_time,room_fee FROM room WHERE room_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            rs = pStmt.executeQuery();
            if (rs.next()) {
                simpleCost += roomId + "," + rs.getDouble(3) +
                        "," + rs.getString(1) + "," + rs.getString(2);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return simpleCost;
    }

    // 详单
    public List<Map<String, Object>> detailCost(String roomId) {
        List<Map<String, Object>> detailCostList = new ArrayList<>();
        try {
            String sql = "SELECT time,room_id,room_occupy,room_now_temp,room_set_temp,room_set_wind,room_tot_fee" +
                    " FROM bill WHERE rooom_id=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setString(1, roomId);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> detailCostInfo = new HashMap<>();
                detailCostInfo.put("time", rs.getString("time"));
                detailCostInfo.put("roomId", rs.getString("room_id"));
                detailCostInfo.put("nowTmp", rs.getDouble("room_now_temp"));
                detailCostInfo.put("setTmp", rs.getDouble("room_set_temp"));
                detailCostInfo.put("setWind", rs.getInt("room_set_wind"));
                detailCostInfo.put("fee", rs.getDouble("room_tot_fee"));
                detailCostList.add(detailCostInfo);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return detailCostList;
    }

    // 报表
    public List<Map<String, Object>> requestReport() {
        List<Map<String, Object>> roomReportList = new ArrayList<>();
        try {
            String sql = "SELECT room_id, room_totalfee FROM room";
            pStmt = connection.prepareStatement(sql);
            rs = pStmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> roomReport = new HashMap<>();
                roomReport.put("roomId", rs.getString("room_id"));
                roomReport.put("fee", rs.getDouble("room_totalfee"));
                roomReportList.add(roomReport);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return roomReportList;
    }

    // 客户端更新温度, 服务端返回当前账单
    public double updateStatus(String token, double nowTmp) {
        double fee = -1;
        try {
            String sql = "UPDATE room SET room_now_temp=? WHERE room_token=?";
            pStmt = connection.prepareStatement(sql);
            pStmt.setDouble(1, nowTmp);
            pStmt.setString(2, token);
            pStmt.executeUpdate();

            String sql2 = "SELECT room_fee FROM room WHERE room_token=?";
            pStmt = connection.prepareStatement(sql2);
            pStmt.setString(1, token);
            rs = pStmt.executeQuery();
            if (rs.next()) {
                fee = rs.getDouble("room_fee");
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (pStmt != null) {
                    pStmt.close();
                }
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return fee;
    }

}
